import datetime
from other.cfg import Config
import pymysql
from loguru import logger


class Synchronization:
    def __init__(self):
        self.cfg = Config()
        # 源数据库连接参数
        self.source_db_config = {
            'host': self.cfg.res['mysql']['host'],
            'user': self.cfg.res['mysql']['user'],
            'password': self.cfg.res['mysql']['password'],
            'database': self.cfg.res['mysql']['database']
        }

        # 目标数据库连接参数
        self.target_db_config = {
            'host': self.cfg.res['target_mysql']['host'],
            'user': self.cfg.res['target_mysql']['user'],
            'password': self.cfg.res['target_mysql']['password'],
            'database': self.cfg.res['target_mysql']['database']
        }
        # self.target_db_config = {
        #     'host': '192.168.10.112',
        #     'user': 'root',
        #     'password': 'Nlycloud.1',
        #     'database': 'qinshan'
        # }
        try:
            self.db = pymysql.connect(**self.source_db_config, cursorclass=pymysql.cursors.DictCursor)
            # 创建游标对象
            self.cursor = self.db.cursor()
            # print("连接成功！")
        except:
            print("连接失败！")

    # 查询获取表字段信息
    def construction(self, table_name):
        # 执行 SQL 查询获取表字段信息
        sql = f"DESCRIBE {table_name}"
        self.cursor.execute(sql)
        # 获取查询结果
        result = self.cursor.fetchall()
        synchronize_exists = False
        for res in result:
            for key, value in res.items():
                if key == 'Field' and value == 'synchronize':
                    synchronize_exists = True
        return synchronize_exists

    def DS(self, table_name):
        table_name = table_name
        if table_name == 'tool_info':
            self.tool_info_ds()
            return True
        synchronize_exists = self.construction(table_name)
        # print(synchronize_exists)
        if synchronize_exists:
            sql = "SELECT * FROM `{}` WHERE `synchronize`='1'".format(table_name)
            # print('synchronize_sql', sql)
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
            # print(res)
            if res != ():
                print('连接到目标数据库')
                # 连接到目标数据库
                target_conn = pymysql.connect(**self.target_db_config, cursorclass=pymysql.cursors.DictCursor)
                print('连接到目标数据库11111')
                # 将检索到的数据插入到目标数据库中
                with target_conn.cursor() as cursor:
                    results = []
                    for item in res:
                        synchronize = '0'  # 将 synchronize 字段的值更改为 '0'
                        for key, value in item.items():
                            if isinstance(value, datetime.datetime):
                                item[key] = str(value)  # 将值为 datetime.datetime 类型的字段转换为字符串类型
                            if key == 'synchronize':
                                item[key] = synchronize
                        values = tuple(item.values())
                        values_str = ", ".join(
                            [f"'{str(val)}'" if isinstance(val, str) else str(val) for val in values])
                        results.append(f"({values_str})")
                    # 得到最终字符串
                    result = ", ".join(results)
                    # print(result)
                    # print(type(result))
                    # print(f"REPLACE INTO `{table_name}` VALUES {result}")
                    sql = f"REPLACE INTO `{table_name}` VALUES {result}"
                    sql = sql.replace("'None'", "NULL").replace("None", "NULL")
                    # print(sql)
                    try:
                        # 云端同步和本地修改同步字段
                        cursor.execute(sql)
                        self.cursor.execute(sql)
                    except Exception as e:
                        target_conn.rollback()
                        self.db.rollback()
                # 提交更改并关闭连接
                target_conn.commit()
                self.db.commit()
                # target_conn.close()
                # self.db.close()
                print(f"{table_name}数据同步完成")
                logger.info(f"{table_name}数据同步完成")

    # 工具信息表同步
    def tool_info_ds(self):
        try:
            table_name = 'tool_info'
            # synchronize_exists = self.construction(table_name)
            # print(synchronize_exists)
            # if synchronize_exists:
            sql = "SELECT * FROM `{}` WHERE `synchronize`='1'".format(table_name)
            # print('synchronize_sql', sql)
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
            # print(res)
            if res != ():
                # 连接到目标数据库
                target_conn = pymysql.connect(**self.target_db_config, cursorclass=pymysql.cursors.DictCursor)
                # 将检索到的数据插入到目标数据库中
                with target_conn.cursor() as cursor:
                    r_results = []
                    g_results = []
                    # 判断表名是够是tool_info表
                    for item in res:
                        tool_info_num = 0
                        tool_info_useable = 0
                        tool_info_lend = 0
                        tool_info_damage = 0
                        tool_info_loss = 0
                        tool_info_return = 0
                        synchronize = '0'  # 将 synchronize 字段的值更改为 '0'
                        r_tool_info_num = item['tool_info_num']
                        if r_tool_info_num is None:
                            r_tool_info_num = 0
                        r_tool_info_useable = item['tool_info_useable']
                        if r_tool_info_useable is None:
                            r_tool_info_useable = 0
                        r_tool_info_lend = item['tool_info_lend']
                        if r_tool_info_lend is None:
                            r_tool_info_lend = 0
                        r_tool_info_damage = item['tool_info_damage']
                        if r_tool_info_damage is None:
                            r_tool_info_damage = 0
                        r_tool_info_loss = item['tool_info_loss']
                        if r_tool_info_loss is None:
                            r_tool_info_loss = 0
                        r_tool_info_return = item['tool_info_return']
                        if r_tool_info_return is None:
                            r_tool_info_return = 0
                        # 查询云端数据库信息
                        g_sql = "select `tool_info_num`,`tool_info_useable`,`tool_info_lend`,`tool_info_damage`,`tool_info_loss`,`tool_info_return` from `tool_info` where `tool_info_id`='{}'".format(
                            item['tool_info_id'])
                        # print(g_sql)
                        cursor.execute(g_sql)
                        g_res = cursor.fetchall()
                        if g_res != ():
                            g_tool_info_num = g_res[0]['tool_info_num']
                            if g_tool_info_num is None:
                                g_tool_info_num = 0
                            g_tool_info_useable = g_res[0]['tool_info_useable']
                            if g_tool_info_useable is None:
                                g_tool_info_useable = 0
                            g_tool_info_lend = g_res[0]['tool_info_lend']
                            if g_tool_info_lend is None:
                                g_tool_info_lend = 0
                            g_tool_info_damage = g_res[0]['tool_info_damage']
                            if g_tool_info_damage is None:
                                g_tool_info_damage = 0
                            g_tool_info_loss = g_res[0]['tool_info_loss']
                            if g_tool_info_loss is None:
                                g_tool_info_loss = 0
                            g_tool_info_return = g_res[0]['tool_info_return']
                            if g_tool_info_return is None:
                                g_tool_info_return = 0
                            # 计算数据
                            tool_info_num = g_tool_info_num + r_tool_info_num
                            tool_info_useable = g_tool_info_useable + r_tool_info_useable
                            tool_info_lend = g_tool_info_lend + r_tool_info_lend
                            tool_info_damage = g_tool_info_damage + r_tool_info_damage
                            tool_info_loss = g_tool_info_loss + r_tool_info_loss
                            tool_info_return = g_tool_info_return + r_tool_info_return
                        # 拼接云端数据sql
                        for key, value in item.items():
                            if isinstance(value, datetime.datetime):
                                item[key] = str(value)  # 将值为 datetime.datetime 类型的字段转换为字符串类型
                            if key == 'synchronize':
                                item[key] = synchronize
                            if key == 'tool_info_num':
                                item[key] = tool_info_num
                            if key == 'tool_info_useable':
                                item[key] = tool_info_useable
                            if key == 'tool_info_lend':
                                item[key] = tool_info_lend
                            if key == 'tool_info_damage':
                                item[key] = tool_info_damage
                            if key == 'tool_info_loss':
                                item[key] = tool_info_loss
                            if key == 'tool_info_return':
                                item[key] = tool_info_return
                        g_values = tuple(item.values())
                        g_values_str = ", ".join(
                            [f"'{str(val)}'" if isinstance(val, str) else str(val) for val in g_values])
                        g_results.append(f"({g_values_str})")
                        # 拼接本地数据sql
                        for key, value in item.items():
                            if key == 'tool_info_num':
                                item[key] = 0
                            if key == 'tool_info_useable':
                                item[key] = 0
                            if key == 'tool_info_lend':
                                item[key] = 0
                            if key == 'tool_info_damage':
                                item[key] = 0
                            if key == 'tool_info_loss':
                                item[key] = 0
                            if key == 'tool_info_return':
                                item[key] = 0
                        r_values = tuple(item.values())
                        r_values_str = ", ".join(
                            [f"'{str(val)}'" if isinstance(val, str) else str(val) for val in r_values])
                        r_results.append(f"({r_values_str})")
                    # 得到最终字符串
                    g_results = ", ".join(g_results)
                    r_results = ", ".join(r_results)
                    g_sql = f"REPLACE INTO `{table_name}` VALUES {g_results}"
                    r_sql = f"REPLACE INTO `{table_name}` VALUES {r_results}"
                    g_sql = g_sql.replace("'None'", "NULL").replace("None", "NULL")
                    r_sql = r_sql.replace("'None'", "NULL").replace("None", "NULL")
                    # print('g_sql', g_sql)
                    # print('r_sql', r_sql)
                    try:
                        # 云端同步和本地修改同步字段
                        cursor.execute(g_sql)
                        self.cursor.execute(r_sql)
                    except Exception as e:
                        target_conn.rollback()
                        self.db.rollback()
                # 提交更改并关闭连接
                target_conn.commit()
                self.db.commit()
                # target_conn.close()
                # self.db.close()
                print(f"{table_name}数据同步完成")
                logger.info(f"{table_name}数据同步完成")
        except Exception as e:
            print(e)
            logger.error(f"tool_info数据同步失败")

    def __del__(self):
        self.cursor.close()
        self.db.close()
